﻿using System.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace FileServerProject
{
    public class UserOperations
    {
        public static FilesAndFolders[] GetFilesAndFoldersData(int? folderID, int currentPage, int itemsPerPage)
        {
            FilesAndFolders[] result = new FilesAndFolders[itemsPerPage];
            FileServerEntitiesModel dbContext = new FileServerEntitiesModel();
            using (dbContext)
            {
                SqlConnection dbCon = new SqlConnection(
                  "Server=.\\SQLEXPRESS; " +
                  "Database=FileServer; " +
                  "Integrated Security=true");
                dbCon.Open();
                using (dbCon)
                {
                    string nativeSQLQuery = "WITH OrdersRN AS " +
                        "( " +
                            "SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowNum " +
                                  ",ID " +
                                  ",Name " +
                                  ",OwnerID " +
                                  ",DateCreated " +
                                  ",ParentID " +
                                  ",DownloadCount " +
                                  ",LastDownloadDate " +
                              "FROM dbo.FilesAndFolders " +
                              "WHERE ParentID = @ParentID " +
                        ") " +

                        "SELECT *  " +
                          "FROM OrdersRN " +
                         "WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1  " +
                                          "AND @PageNum * @PageSize " +
                         "ORDER BY RowNum; ";
                    SqlCommand cmd = new SqlCommand(nativeSQLQuery, dbCon);
                    cmd.Parameters.Add(new SqlParameter("@ParentID", folderID));
                    cmd.Parameters.Add(new SqlParameter("@PageNum", currentPage));
                    cmd.Parameters.Add(new SqlParameter("@PageSize", itemsPerPage));
                    var pageFiles = cmd.ExecuteReader();
                    foreach (var item in pageFiles)
                    {
                        System.Console.WriteLine(item);
                    }
                }
            }

            return result;
        }

        public static void AddUpdateUser(User user)
        {
            FileServerEntitiesModel dbContext = new FileServerEntitiesModel();
            using (dbContext)
            {
                dbContext.Add(user);
                dbContext.SaveChanges();
            }
        }

        public static User SelectUser(User user)
        {
            User selectedUser = new User();
            FileServerEntitiesModel dbContext = new FileServerEntitiesModel();
            using (dbContext)
            {
                selectedUser = dbContext.Users.FirstOrDefault(u => u.UserName == user.UserName && u.Password==user.Password);
            }
            return selectedUser;
        }
        
        public static void DeleteUser(User user)
        {
            FileServerEntitiesModel dbContext = new FileServerEntitiesModel();
            using (dbContext)
            {
                dbContext.Delete(user);
                dbContext.SaveChanges();
            }
        }

        public static List<User> GetAllUsers()
        {
            List<User> allUsers = new List<User>();
            FileServerEntitiesModel dbContext = new FileServerEntitiesModel();
            using (dbContext)
            {
                allUsers = dbContext.Users.ToList();
            }
            return allUsers;
        }

        public static FilesAndFolders GetUserFolder(string userName)
        {
            FilesAndFolders userParentFolder;
            FileServerEntitiesModel dbContext = new FileServerEntitiesModel();
            using (dbContext)
            {
                userParentFolder = dbContext.FilesAndFolders.Single(f => f.User.UserName == userName && f.ParentID==null);
            }
            return userParentFolder;
        }

        public static FilesAndFolders GetParentFolder(int folderID)
        {
            FilesAndFolders parentFolder;
            FileServerEntitiesModel dbContext = new FileServerEntitiesModel();
            using (dbContext)
            {
                FilesAndFolders folder = dbContext.FilesAndFolders.Single(f => f.ID == folderID);
                parentFolder = dbContext.FilesAndFolders.Single(f => f.ID == folder.ParentID);
            }
            return parentFolder;
        }

        public static FilesAndFolders GetFolderByID(int folderID)
        {
            FilesAndFolders folder;
            FileServerEntitiesModel dbContext = new FileServerEntitiesModel();
            using (dbContext)
            {
                folder = dbContext.FilesAndFolders.Single(f => f.ID == folderID);
            }
            return folder;
        }

        public static void AddUpdateFile(FilesAndFolders file) 
        {
            FileServerEntitiesModel dbContext = new FileServerEntitiesModel();
            using (dbContext)
            {
                dbContext.Add(file);
                dbContext.SaveChanges();
            }
        }

        public static void AddUpdateFolder(FilesAndFolders folder)
        {
            FileServerEntitiesModel dbContext = new FileServerEntitiesModel();
            using (dbContext)
            {
                dbContext.Add(folder);
                dbContext.SaveChanges();
            }
        }

        public static FilesAndFolders SelectFile(FilesAndFolders file)
        {
            FilesAndFolders selectedFile = new FilesAndFolders();
            FileServerEntitiesModel dbContext = new FileServerEntitiesModel();
            using (dbContext)
            {
                selectedFile = dbContext.FilesAndFolders.FirstOrDefault(f => f.ID == file.ID);
            }
            return selectedFile;
        }

        public static FilesAndFolders SelectFolder(FilesAndFolders folder)
        {
            FilesAndFolders selectedFile = new FilesAndFolders();
            FileServerEntitiesModel dbContext = new FileServerEntitiesModel();
            using (dbContext)
            {
                selectedFile = dbContext.FilesAndFolders.FirstOrDefault(f => f.ID == folder.ID);
            }
            return selectedFile;
        }

        public static void DeleteFileOrFolder(int  fileOrFolderID)
        {
            FileServerEntitiesModel dbContext = new FileServerEntitiesModel();
            using (dbContext)
            {
                FilesAndFolders selected = dbContext.FilesAndFolders.FirstOrDefault(f => f.ID == fileOrFolderID);
                dbContext.Delete(selected);
                dbContext.SaveChanges();
            }
        }

        public static List<FilesAndFolders> GetFolderFolders(int parentFolderId)
        {
            List<FilesAndFolders> folders = new List<FilesAndFolders>();
            FileServerEntitiesModel dbContext = new FileServerEntitiesModel();
            using (dbContext)
            {
                folders = dbContext.FilesAndFolders.Where<FilesAndFolders>(f => f.ParentID == parentFolderId && f.DownloadCount == null).ToList();
            }
            return folders;
        }

        public static List<FilesAndFolders> GetFolderFiles(int parentFolderID)
        {
            List<FilesAndFolders> folders = new List<FilesAndFolders>();
            FileServerEntitiesModel dbContext = new FileServerEntitiesModel();
            using (dbContext)
            {
                folders = dbContext.FilesAndFolders.Where(f => f.ParentID == parentFolderID && f.DownloadCount!=null).ToList();
            }
            return folders;
        }
    }
}